package beans;

import java.sql.*;

/**
 * JDBC class to use a Postgres database.
 *
 * @author dshpak
 */
public class PostgresDB implements java.io.Serializable {
    /** Name of the host on the network */
    protected String hostName;
    /** Name of the database on the host */
    protected String dbName;
    /** User name for database */
    protected String userName;
    /** Database password */
    protected String password;
    /** Port for the network connection */
    protected int port;
    /** Connection to the database */
    protected Connection db = null;

    /**
     * Constructor loads the database driver jar
     *
     * @param hostName Name or IP address of host
     * @param dbName Name of database on host
     * @param userName Database user name
     * @param password Password for the database
     */
    public PostgresDB () throws ClassNotFoundException {
        // load the JDBC driver for Postgres
        Class.forName("org.postgresql.Driver");
    }
    
    public void setHost(String hostName) {
        this.hostName = hostName;
    }
    
    public void setPort(int port) {
        this.port = port;
    }
    
    public void setDatabase(String dbName) {
        this.dbName = dbName;
    }
    
    public void setUserName(String userName) {
        this.userName = userName;
    }
    
    public void setPass(String password) {
        this.password = password;
    }
    
    /**
     * Opens a connection to the database
     *
     * @return Connection to the database
     */
    public Connection getConnection() throws SQLException {
        if (db == null) {
            // connect to the datbase server over TCP/IP
            // (requires that you edit pg_hba.conf
            // as shown in the "Authentication" section of this article)
            String url = "jdbc:postgresql://"+hostName+":"+port+"/" + dbName;
            db = DriverManager.getConnection(url, userName, password);
        }
        return db;
    }

    /**
     * Tries to open a connection to the database but times out
     * after a specified number of seconds
     *
     * @param timeoutSeconds Number of seconds for timeout
     *
     * @return Connection to the database
     *
     * @throws DatabaseTimeoutException If it takes too long
     * @throws SQLException If there is an error from the database server
     */
    public Connection getConnectionWithTimeout(int timeoutSeconds)
            throws SQLException, DatabaseTimeoutException {
        ConnectThread  connectThread = null;
        Boolean sleeping = new Boolean (true);

        if (db == null) {
            // connect to the datbase server over TCP/IP
            // (requires that you edit pg_hba.conf
            // as shown in the "Authentication" section of this article)
            try {
                connectThread = new ConnectThread(sleeping);
                Thread.sleep (1000*timeoutSeconds);
                /* The synchronization object is simply a unique Object identifier.
                 */
                synchronized (connectThread) {
                    sleeping = new Boolean(false);
                    throw new DatabaseTimeoutException ();
                }
            }
            catch (InterruptedException e) {
                // Normal operation is to for the
            }
        }
        return db;
    }

    /**
     * A Thread that connects to the database host.
     * The parent thread can do a sleep() so that we
     * can timeout on trying to connect
     */
    private class ConnectThread extends Thread {
        /** Keep track of parent since we need to interrupt() */
        private Thread parentThread = null;
        /** We don't want to interrupt the parent if it is not sleeping!! */
        private Boolean parentSleeping = null;

        /**
         * Create the ConnectThread object
         *
         * @param sleeping The parent thread must pass in this Boolean set to true
         *
         */
        public ConnectThread (Boolean sleeping){
            parentThread = Thread.currentThread();
            this.parentSleeping = sleeping;
            start();
        }

        /*
         * Get the database Connection and then interrupt the parent Thread
         * if it is still sleeping
         **/
        public void run() {
            try {
                String url = "jdbc:postgresql://"+hostName+":"+port+"/" + dbName;
                db = DriverManager.getConnection(url, userName, password);
            }
            catch (SQLException e) {
                System.out.println("Unable to connect to database " + e);
                e.printStackTrace();
            }
            synchronized (this) {
                /* Interrupt the parent thread only if it is still sleeping */
                if (parentSleeping.booleanValue() == true) parentThread.interrupt();
            }
        }
    }


    /**
     * Gets the results for a database query
     *
     * @param query The SQL query string
     * @return The results
     * @throws SQLException If there is an error from the database server
     */
    public ResultSet getResults (String query) throws SQLException {
        Statement stmt = null;

        if (query == null) return null;

        if (db == null) {
            throw (new SQLException ("Postgres query: No database connection"));
        }
        try {
            stmt = db.createStatement();
        }
        catch (SQLException e) {
            throw new SQLException ("Postgres query: Unable to create Statement");
        }
        ResultSet results = stmt.executeQuery(query);

        return results;
    }

    /**
     * Gets the results for a database query using a specified timeout
     *
     * @param query The SQL query string
     * @param timeoutSeconds Number of seconds for timeout
     * @return The results
     *
     * @throws DatabaseTimeoutException If it takes too long
     * @throws SQLException If there is an error from the database server
     */
    public ResultSet getResultsWithTimeout (String query, int timeoutSeconds)
        throws SQLException, DatabaseTimeoutException {
        QueryThread queryThread = null;
        Statement stmt = null;
        Boolean sleeping = new Boolean (true);

        if (query == null) return null;

        try {
            if (db == null) {
                throw (new SQLException ("Postgres query: No database connection"));
            }
            try {
                stmt = db.createStatement();
            }
            catch (SQLException e) {
                throw new SQLException ("Postgres query: Unable to create Statement");
            }
        }
        catch (SQLException e) {
            System.out.println("Unable to connect to database " + e);
            e.printStackTrace();
        }

        try {
            queryThread = new QueryThread (query, stmt, sleeping);
            Thread.sleep (1000*timeoutSeconds);
            synchronized (queryThread) {
                sleeping = new Boolean(false);
            }
        }
        catch (InterruptedException e) {
            ResultSet results = queryThread.getResults();
            if (queryThread.getException() != null) {
                throw queryThread.getException();
            }
            return results;
        }

        return null;
    }


    /**
     * A Thread that queries the database host.
     * The parent thread can do a sleep() so that we
     * can timeout on trying to get the ResultSet
     * from the database
     */
    private class QueryThread extends Thread {
        /** Keep track of parent since we need to interrupt() */
        private Thread parentThread = null;
        /** We don't want to interrupt the parent if it is not sleeping!! */
        private Boolean parentSleeping = null;
        /** The results are extracted after the query using getResults() */
        private ResultSet results = null;
        /** Need a valid statement for the constructor */
        private Statement stmt = null;
        /** The query string */
        private String query = null;
        /** Track and exception if it occurs */
        private SQLException ex = null;

        /**
         * Create the ConnectThread object
         *
         * @param query The SQL query string
         * @param stmt A valid SQL Statement object
         * @param sleeping The parent thread must pass in this Boolean set to true
         *
         */
        public QueryThread (String query, Statement stmt, Boolean sleeping){
            parentThread = Thread.currentThread();
            this.query = query;
            this.stmt = stmt;
            this.parentSleeping = sleeping;
            start();
        }

        /**
         * Perform the SQL query and notify the parent Thread
         * if it is still sleeping
         */
        @Override
        public void run() {
            try {
                results = stmt.executeQuery(query);
            }
            catch (SQLException e) {
                ex = e;
            }
            synchronized (this) {
                if (parentSleeping.booleanValue() == true) parentThread.interrupt();
            }
        }

        /**
         * Allows for multiple Query threads for one database connection.
         * More of a safety measure.
         *
         * @return Results of the query
         */
        public ResultSet getResults () { return results; }

        /**
         * Gets any Exceptions from the QueryThread's execution
         *
         * @return Exceptions from the QueryThread's execution
         */
        public SQLException getException() { return ex; }
    }


}